cap program drop tab_admincosts
program define tab_admincosts
	
	shell mkdir -p "$release/tables/admincosts/"

	use "$mlr_data_path/cleaned_mlr_allstates.dta", clear
	drop if payer_id=="M0099" 
	
	* Section-level costs
	local sec1 "1 4 5"
	local sec2 "7 13 14 15 16 17"
	local sec3 "18 19 20 21 22 23 24 25 26"
	local sec4 "27 28 29 30 31 32"
	local sec5 "33 34 35 36 37 38 39"
	local sec7 "43 44 45 46 47"
	local secnumerator "280 281 282 283 284 285"
	local secdenominator "287 288 289"
	local secrebate "323"
	local misc "292 311"
	local varsums // stuff we want to sum over
	foreach item in `sec1' `sec2' `sec3' `sec4' `sec5' `sec7' {
		local append = "value`item'_YEARLY"
		replace `append' = 0 if `append' < 0
		local varsums `varsums' `append' 
	}
	foreach item in `secnumerator' `secdenominator' `secrebate' {
		local append = "value`item'_TOTAL"
		local varsums `varsums' `append'
	}
	collapse (sum) `varsums' per* indiv_mlr indiv_subs claims_only_indiv_mlr, by(report_year short_name carrier_state payer_id market oregon)
	
	foreach secnum in 3 4 5 {
		local sec_sum
		foreach item in `sec`secnum'' {
			local append "+ value`item'_YEARLY"
			local sec_sum `sec_sum' `append'
		}
		gen sec`secnum'_sum = 0 `sec_sum'
		gen sec`secnum'_ymean = sec`secnum'_sum / value47_YEARLY
		gen sec`secnum'_mmean = sec`secnum'_sum / value46_YEARLY
	}
	
	gen admin_sum_mmean = sec3_mmean + sec4_mmean + sec5_mmean
	gen lifeyear = value47_YEARLY
	gen active = (value47_YEARLY > 0)
	
	* Average enrollment in Oregon by insurance market
	local collapse_spell = ""
	local dem_vars = "admin_sum_mmean sec3_mmean sec4_mmean sec5_mmean lifeyear"
	foreach var in `dem_vars' {
		local newspell = "(mean) mean_`var'=`var' (sd) sd_`var'=`var' (p50) p50_`var'=`var'"
		local collapse_spell = "`collapse_spell' `newspell'"
	}
	#delimit ;
	collapse 
		`collapse_spell' 
		, by(market oregon)
	;
	#delimit cr
	
	reshape long mean_ sd_ p50_ , i(market oregon) j(dem_var, string)
	drop if market != "INDIVIDUAL" & (dem_var != "lifeyear")
	drop if oregon == 0
	
	
	* This is just to order row for when we output the table
	local dem_vars = "admin_sum_mmean sec3_mmean sec4_mmean sec5_mmean lifeyear count"
	gen orderer = 0
	local i = 0
	foreach w in `dem_vars' {
		replace  orderer = `i' if dem_var == "`w'"
		local i = `i' + 1
	}
	local i = 0
	foreach w in "INDIVIDUAL" "SMALLGROUP" "LARGEGROUP" "OTHER" {
		replace  orderer = orderer + `i' if dem_var == "lifeyear" & market == "`w'"
		local i = `i' + 1
	}
	replace orderer = 1000 if dem_var == "count"
	sort orderer
	drop orderer oregon
	
	export delimited using "$release/tables/admincosts/main.csv", replace

end

cap program drop tab_admincosts_counts
program define tab_admincosts_counts
	* Count the number of states that a payer is active in 
	use "$mlr_data_path/cleaned_mlr_allstates.dta", clear
	drop if report_year == 2017
	drop if payer_id=="M0099" | market != "INDIVIDUAL"
	gen active = (value47_YEARLY > 0)
	collapse (sum) active , by(payer_id report_year)
	#delimit ;
	collapse 
		(mean) mean_count=active
		(p50) p50_count=active
		(sd)  sd_count=active
	;
	#delimit cr
	gen market = "INDIVIDUAL"
	reshape long mean_ sd_ p50_ , i(market) j(dem_var, string)
	export delimited using "$release/tables/admincosts/counts.csv", replace
end

